﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using DHQGHN.DAL.Base;
using DHQGHN.DAL.Common;
using DHQGHN.DAL.Models.Admin;


namespace DHQGHN.DAL.DAO.Admin
{
    public class UserDAO : SqlDAO
    {
        public UserModel entity { get; set; }

        #region constructor
        public UserDAO(String connectionString, System.Type type = null)
            : base(connectionString, type) { ;}
        #endregion

        #region Procedure Name in Database
        public const string SP_GET_USER = "[spGetUser]";
        public const string SP_USERNAME_IS_UNIQUE = "[up_Users_isUnique]";
        public const string SP_GET_USER_BY_USERNAME = "[spGetUserIdByUserName]";
        public const string SP_GET_LIST_PAGING_USER = "spGetListPagingUser";
        public const string SP_GET_LIST_PAGING_USER_D = "spGetListPagingUserd";
        public const string SP_GET_ALL_USER = "spGetAllUser";
        public const string SP_CREATE_USER = "spCreateUser";
        public const string SP_REGISTER_USER = "spRegisterUser";
        public const string SP_DELETE_USER = "spDeleteUser";
        public const string SP_DELETE_USER_SERVICE = "spDeleteUserService";
        public const string SP_GET_ACTIVE = "spGetActive";
        public const string SP_GET_CODE_GROUP_BYUSERID = "spGetCodeGroupByUserId";
        public const string SP_GET_USER_BY_ID = "spGetUserById";
        public const string SP_GET_USER_BY_GROUP_ID = "spGetUserByGroupId";
        public const string SP_GET_ISACTIVE_BY_ID = "spGetIsActiveById";
        public const string SP_GET_USER_NAME_BY_USER_ID = "spGetUserNameByUserId";
        public const string SP_RE_ACTIVE_USER = "spReActiveUser";
        public const string SP_RESET_PASSWORD = "spResetPassword";
        public const string SP_LOGIN = "spLogin";
        public const string SP_CHECK_EMAIL = "spCheckEmail";
        public const string SP_GET_USER_BY_USERNAME_APPCODE = "spGetUserIdByUserNameAndAppCode";
        public const string SP_UPDATE_USER = "spUpdateUser";
        public const string SP_RESET_USER = "spResetPasswordByforget";
        public const string SP_GET_PASSWORD_SALT = "spGetPasswordSaltByUserNameAndAppCode";
        public const string SP_GET_PASSWORD_SALT_BY_USER_ID = "spGetPasswordSaltByUserId";
        #endregion

        #region procedureParameter
        public const string Param_FullName = "@FullName";
        public const string Param_UserName = "@UserName";
        public const string Param_Password = "@Password";
        public const string Param_USER_ID = "@Id";
        public const string Param_Group_Id = "@GroupId";
        public const string Param_Status = "@Status";
        public const string Param_AppId = "@AppId";
        public const string Param_Email = "@Email";
        public const string Param_AppCode = "@AppCode";
        public const string Param_Password_Salt = "@PasswordSalt";
        #endregion

        public bool CreateUser(UserModel user)
        {
            try
            {
                string salt = CreateSalt();
                SqlCommand cmd = this.GetSprocCommand(SP_CREATE_USER);
                cmd.Parameters.Add(new SqlParameter(Param_USER_ID, user.ID));
                cmd.Parameters.Add(CreateParameter(Param_UserName, user.UserName));
                cmd.Parameters.Add(CreateParameter(Param_FullName, user.FullName));
                //cmd.Parameters.Add(CreateParameter(Param_Password, DHQGHN.Common.Utils.MD5Encode(user.Password)));
                cmd.Parameters.Add(CreateParameter(Param_Password, DHQGHN.Common.Utils.CreatePasswordHash(user.Password+ salt)));
                
                cmd.Parameters.Add(CreateParameter(Param_Email, user.Email));
                cmd.Parameters.Add(CreateParameter(Param_AppCode, user.AppCode));
                cmd.Parameters.Add(CreateParameter(Param_AppId, user.AppId));
                cmd.Parameters.Add(CreateParameter(Param_Password_Salt, salt));

                int result = ExecuteNonQuery(cmd);
                return result > 0 ? true : false;
            }
            catch
            {
                throw;
            }
        }

        public bool RegisterUser(DHQGHN.DAL.BO.Admin.USER_GROUP_CREATE user)
        {
            try
            {
                string salt =  CreateSalt();
                SqlCommand cmd = this.GetSprocCommand(SP_REGISTER_USER);                
                cmd.Parameters.Add(CreateParameter(Param_UserName, user.UserName));
                cmd.Parameters.Add(CreateParameter(Param_FullName, user.FullName));
                cmd.Parameters.Add(CreateParameter(Param_Email, user.Email));
                //cmd.Parameters.Add(CreateParameter(Param_Password, DHQGHN.Common.Utils.MD5Encode(user.Password +salt)));                
                cmd.Parameters.Add(CreateParameter(Param_Password, 
                    DHQGHN.Common.Utils.CreatePasswordHash(user.Password + salt)));
                cmd.Parameters.Add(CreateParameter(Param_AppCode, user.AppCode));
                cmd.Parameters.Add(CreateParameter(Param_Password_Salt, salt));

                int result = ExecuteNonQuery(cmd);

                return result > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }

        public bool ResetPassWordByforget(DHQGHN.DAL.BO.Admin.USER_GROUP_CREATE user)
        {
            try
            {
                string salt = GetPasswordSalt(user.UserName, user.AppCode);
                SqlCommand cmd = this.GetSprocCommand(SP_RESET_USER);

                cmd.Parameters.Add(CreateParameter(Param_UserName, user.UserName));
                cmd.Parameters.Add(CreateParameter(Param_Email, user.Email));
                cmd.Parameters.Add(CreateParameter(Param_Password,
                    DHQGHN.Common.Utils.CreatePasswordHash(user.Password + salt)));
                    //DHQGHN.Common.Utils.MD5Encode(user.Password)));
                cmd.Parameters.Add(CreateParameter(Param_AppCode, user.AppCode));

                int result = ExecuteNonQuery(cmd);

                return result > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }

        public bool UpdateUser (DHQGHN.DAL.BO.Admin.USER_GROUP_CREATE user)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(SP_UPDATE_USER);
                cmd.Parameters.Add(CreateParameter(Param_UserName, user.UserName));
                cmd.Parameters.Add(CreateParameter(Param_FullName, user.FullName));
                cmd.Parameters.Add(CreateParameter(Param_Email, user.Email));
                cmd.Parameters.Add(CreateParameter(Param_AppCode, user.AppCode));

                int result = ExecuteNonQuery(cmd);

                return result > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }

        public bool UpdateUser_DHQG(DHQGHN.DAL.BO.Admin.USER_GROUP_CREATE user)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand("spUpdateUser_DHQG");
                cmd.Parameters.Add(CreateParameter(Param_UserName, user.UserName));
                cmd.Parameters.Add(CreateParameter("@Username_Old", user.UserName_Old));
                cmd.Parameters.Add(CreateParameter(Param_FullName, user.FullName));
                cmd.Parameters.Add(CreateParameter(Param_Email, user.Email));
                cmd.Parameters.Add(CreateParameter(Param_AppCode, user.AppCode));

                int result = ExecuteNonQuery(cmd);

                return result > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }

        public bool actionCreateUserInGroupByListGroup(UserInListGroup ea)
        {
            try
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("GroupId", typeof(int));
                for (int i = 0; i < ea.ListGroup.Length; i++)
                {
                    dt.Rows.Add(Convert.ToInt32(ea.ListGroup[i]));
                }
                SqlCommand cmd = this.GetSprocCommand("spCreateUserInGroupByUserName");
                cmd.Parameters.Add(new SqlParameter("@UserName", ea.UserName));
                cmd.Parameters.Add(new SqlParameter("@ListGroup", dt));
                cmd.Parameters.Add(new SqlParameter("@AppCode", ea.AppCode));

                int result = ExecuteNonQuery(cmd);
                return result > 0 ? true : false;
            }
            catch
            {
                throw;
            }
        }

        public bool CheckUserExist(string userName, int appId)
        {
            try
            {
                SqlCommand cmd = GetSprocCommand("spGetUserByUserNameAppId");
                cmd.Parameters.Add(CreateParameter(Param_UserName, userName));
                cmd.Parameters.Add(CreateParameter(Param_AppId, appId));
                Dictionary<string, string> mapDictionary = new Dictionary<string, string>();
                mapDictionary.Add("UserName", "Username");
                mapDictionary.Add("AppId", "AppId");

                List<UserModel> items = GetList<UserModel>(cmd, mapDictionary);

                return items.Count() > 0 ? true : false;
            }
            catch
            {
                throw;
            }
        }

        public bool ResetPassword(int userId, string PassWord)
        {
            try
            {
                string salt = GetPasswordSalt(userId);
                //string passwordEncode = DHQGHN.Common.Utils.MD5Encode(PassWord);
                string passwordEncode = DHQGHN.Common.Utils.CreatePasswordHash(PassWord+salt);
                
                SqlCommand cmd = this.GetSprocCommand(SP_RESET_PASSWORD);
                cmd.Parameters.Add(CreateParameter(Param_USER_ID, userId));
                cmd.Parameters.Add(CreateParameter(Param_Password, passwordEncode));
                int result = ExecuteNonQuery(cmd);
                return result > 0 ? true : false;
            }
            catch
            {
                throw;
            }
        }

        public bool Login(string userName, string password, string appCode, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(SP_LOGIN);
                cmd.Parameters.Add(CreateParameter(Param_UserName, userName));
                cmd.Parameters.Add(CreateParameter(Param_Password, DHQGHN.Common.Utils.CreatePasswordHash(password)));
                cmd.Parameters.Add(CreateParameter(Param_AppCode, appCode));

                List<UserModel> items = this.GetList<UserModel>(cmd, mapDictionary);

                return items.Count > 0 ? true : false;
            }
            catch
            {
                throw;
            }
        }

        public bool CheckEmail(string Email, string AppCode)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(SP_CHECK_EMAIL);
                cmd.Parameters.Add(CreateParameter(Param_Email, Email));
                cmd.Parameters.Add(CreateParameter(Param_AppCode, AppCode));
                int result = Convert.ToInt32(ExecuteScalar(cmd));
                return result > 0 ? false : true;
            }
            catch
            {
                throw;
            }
        }

        public UserModel GetUserInfo(string userName, string appCode, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(SP_GET_USER_BY_USERNAME_APPCODE);
                cmd.Parameters.Add(CreateParameter(Param_UserName, userName));
                cmd.Parameters.Add(CreateParameter(Param_AppCode, appCode));

                return GetSingle<UserModel>(cmd, mapDictionary);
            }
            catch
            {
                throw;
            }
        }

        internal bool checkValidUser(string _username, string _password, Dictionary<String, String> mapDictionary)
        {
            SqlCommand command = this.GetSprocCommand(SP_GET_USER);
            command.Parameters.Add(CreateParameter(Param_UserName, _username));
            command.Parameters.Add(CreateParameter(Param_Password, DHQGHN.Common.Utils.SHA1Encode(_password)));
            UserModel u = GetSingle<UserModel>(command, mapDictionary);
            return u != null ? true : false;
        }

        internal DataPage<UserModel> GetDynamicListPaging(UserModel model, int pageSize, int pageIndex, Dictionary<String, String> mapDictionary, bool compareEqual)
        {
            try
            {
                //SqlCommand command = SharedSqlDao.GetSprocCommand(pr_Person_GetAll);
                SqlCommand command = this.GetSprocCommand(SP_GET_LIST_PAGING_USER);
                if (model != null)
                {
                    command.Parameters.Add(CreateParameter(Param_FullName, compareEqual ? model.FullName : ("%" + model.FullName + "%")));
                    command.Parameters.Add(CreateParameter(Param_UserName, compareEqual ? model.UserName : ("%" + model.UserName + "%")));
                    command.Parameters.Add(new SqlParameter(Param_Status, model.Status));
                    command.Parameters.Add(new SqlParameter(Param_AppId, model.AppId));
                }
                command.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
                command.Parameters.Add(new SqlParameter("@PageSize", pageSize));

                return GetDataPage<UserModel>(command, pageIndex, pageSize, mapDictionary);
            }
            catch
            {
                throw;
            }
        }

        internal DataPage<UserModel> GetDynamicListPagingd(UserModel model, int pageSize, int pageIndex, Dictionary<String, String> mapDictionary, bool compareEqual)
        {
            try
            {
                //SqlCommand command = SharedSqlDao.GetSprocCommand(pr_Person_GetAll);
                SqlCommand command = this.GetSprocCommand(SP_GET_LIST_PAGING_USER_D);
                if (model != null)
                {
                    command.Parameters.Add(CreateParameter(Param_FullName, compareEqual ? model.FullName : ("%" + model.FullName + "%")));
                    command.Parameters.Add(CreateParameter(Param_UserName, compareEqual ? model.UserName : ("%" + model.UserName + "%")));
                    command.Parameters.Add(CreateParameter(Param_AppId, model.AppId));
                }
                command.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
                command.Parameters.Add(new SqlParameter("@PageSize", pageSize));



                return GetDataPage<UserModel>(command, pageIndex, pageSize, mapDictionary);
            }
            catch
            {
                throw;
            }
        }

        internal UserModel GetUserByUserName(string userName, Dictionary<string, string> mapDictionary)
        {
            SqlCommand command = this.GetSprocCommand(SP_GET_USER_BY_USERNAME);
            command.Parameters.Add(CreateParameter(Param_UserName, userName));
            UserModel u = GetSingle<UserModel>(command, mapDictionary);
            return u;
        }

        internal string GetCodeGroupByUser(int userID)
        {
            SqlCommand command = this.GetSprocCommand(SP_GET_CODE_GROUP_BYUSERID);
            command.Parameters.Add(CreateParameter(Param_USER_ID, userID));
            return GetSingleString(command);
        }

        internal List<UserModel> GetAll(int? AppId,Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand command = this.GetSprocCommand(SP_GET_ALL_USER);
                command.Parameters.Add(new SqlParameter("@AppId", AppId));
                return GetList<UserModel>(command, mapDictionary);
            }
            catch
            {
                throw;
            }
        }

        internal bool createOrUpdateGroup(UserModel userModel)
        {
            try
            {
                SqlCommand command = this.GetSprocCommand(SP_CREATE_USER);
                command.Parameters.Add(CreateParameter(Param_USER_ID, userModel.ID));
                command.Parameters.Add(CreateParameter(Param_UserName, userModel.UserName));
                command.Parameters.Add(CreateParameter(Param_Password, userModel.Password));
                //command.Parameters.Add(new SqlParameter("@" + UserModel.Field_RegDate, userModel.RegDate));
                command.Parameters.Add(CreateParameter(Param_Email, userModel.Email));
                command.Parameters.Add(CreateParameter(Param_FullName, userModel.FullName));
                command.Parameters.Add(CreateParameter(Param_AppId, userModel.AppId));

                return ExecuteQuery(command) > 0 ? true : false;
            }
            catch
            {
                throw;
            }
        }

        internal bool deleteUser(int userId)
        {
            try
            {
                SqlCommand command = this.GetSprocCommand(SP_DELETE_USER_SERVICE);
                command.Parameters.Add(CreateParameter(Param_USER_ID, userId));
                int result = ExecuteQuery(command);
                return result > 0 ? true : false;
            }
            catch
            {
                throw;
            }
        }

        internal bool deleteUser(string[] userName, string appCode)
        {
            try
            {
                SqlCommand command = this.GetSprocCommand(SP_DELETE_USER_SERVICE);
                DataTable tableUserName = GetTableUserName(userName);
                command.Parameters.Add(new SqlParameter("@TableUserName", tableUserName));
                command.Parameters.Add(CreateParameter(Param_AppCode, appCode));

                return ExecuteQuery(command) > 0 ? true : false;
            }
            catch
            {
                throw;
            }
        }

        internal bool Reactive(string userName, string appCode)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand("spUser_Reactive");
                cmd.Parameters.Add(CreateParameter(Param_UserName, userName));
                cmd.Parameters.Add(CreateParameter(Param_AppCode, appCode));

                int result = ExecuteNonQuery(cmd);

                return result > 0 ? true : false;
            }
            catch 
            {
                
                throw;
            }
        }

        internal bool reActiveUser(int userId)
        {
            try
            {
                SqlCommand command = this.GetSprocCommand(SP_RE_ACTIVE_USER);
                command.Parameters.Add(CreateParameter(Param_USER_ID, userId));
                return ExecuteQuery(command) > 0 ? true : false;
            }
            catch
            {
                throw;
            }
        }

        internal bool deleteUserCheck(int[] appId)
        {
            try
            {
                SqlCommand command = this.GetSprocCommand(SP_DELETE_USER);
                command.Parameters.Add(new SqlParameter("@UserList", GetTableId<int>(appId, "Id")));

                return ExecuteQuery(command) > 0 ? true : false;
            }
            catch
            {
                throw;
            }
        }

        internal bool CheckActive(string _username)
        {
            SqlCommand command = this.GetSprocCommand(SP_GET_ACTIVE);
            command.Parameters.Add(CreateParameter(Param_UserName, _username));
            int tmp = GetSingleInt32(command);
            return tmp == 1 ? true : false;

        }

        internal bool isUnique(string _username)
        {
            SqlCommand command = this.GetSprocCommand(SP_USERNAME_IS_UNIQUE);
            command.Parameters.Add(CreateParameter(Param_UserName, _username));
            int tmp = GetSingleInt32(command);
            return tmp == 0 ? true : false;
        }

        internal UserModel GetUserById(int? userId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand command = this.GetSprocCommand(SP_GET_USER_BY_ID);
                command.Parameters.Add(new SqlParameter(Param_USER_ID, userId));

                return GetSingle<UserModel>(command, mapDictionary);
            }
            catch
            {
                throw;
            }
        }

        internal List<UserModel> GetAllUserByGroupId(int groupId, Dictionary<string, string> mapDictionary)
        {

            try
            {
                SqlCommand command = this.GetSprocCommand(SP_GET_USER_BY_GROUP_ID);
                command.Parameters.Add(CreateParameter(Param_Group_Id, groupId));
                return GetList<UserModel>(command, mapDictionary);
            }
            catch
            {
                throw;
            }
        }

        internal int GetIsActiveById(int? userId)
        {
            SqlCommand command = this.GetSprocCommand(SP_GET_ISACTIVE_BY_ID);
            int tmp = GetSingleInt32(command);
            return tmp;
        }

        internal List<UserModel> GetUserNameByUserId(int userId, Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand command = this.GetSprocCommand(SP_GET_USER_NAME_BY_USER_ID);
                command.Parameters.Add(CreateParameter(Param_USER_ID, userId));                
                return GetList<UserModel>(command, mapDictionary);
            }
            catch
            {
                throw;
            }
        }

        internal string GetPasswordSalt(string userName, string appCode)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(SP_GET_PASSWORD_SALT);
                cmd.Parameters.Add(CreateParameter(Param_UserName, userName));
                cmd.Parameters.Add(CreateParameter(Param_AppCode, appCode));

                object result = ExecuteScalar(cmd);
                string salt = result is System.DBNull  ? "" : (string)result;

                return salt;

            }
            catch
            {
                throw;
            }
        }

        internal string GetPasswordSalt(int userId)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand(SP_GET_PASSWORD_SALT_BY_USER_ID);
                cmd.Parameters.Add(CreateParameter(Param_USER_ID, userId));

                object result = ExecuteScalar(cmd);
                string salt = result is System.DBNull ? "" : (string)result;

                return salt;

            }
            catch
            {
                throw;
            }
        }

        private string CreateSalt()
        {
            RandomNumberGenerator rng = RandomNumberGenerator.Create();
            byte[] number = new byte[32];
            rng.GetBytes(number);

            string result = Convert.ToBase64String(number);
            if (result.Length > 128)
            {
                result = result.Substring(0, 128);
            }
            return result;
        }

        private DataTable GetTableUserName(string[] userName)
        {
            DataTable data = new DataTable();
            data.Columns.Add("UserName");
            for(int i= 0;i< userName.Length;i++)
            {
                data.Rows.Add(userName[i]);
            }

            return data;
        }

        public List<UserModel> GetListUserToAsignInGroup(int groupId, int donViId,Dictionary<string, string> mapDictionary)
        {
            try
            {
                SqlCommand cmd = this.GetSprocCommand("spUser_GetUserToAssignInGroup");
                cmd.Parameters.Add(CreateParameter("@GroupId", groupId));
                cmd.Parameters.Add(CreateParameter("@DonViId", donViId));

                return GetList<UserModel>(cmd, mapDictionary);

            }
            catch 
            {
                
                throw;
            }
        }
        
        }
}
